Import the necessary python packages and define functions and objects for cleaning the data.
import pandas as pd
import numpy as np
import openpyxl
import requests
import json
import csv
import plotly.express as px
from pandas.core.common import SettingWithCopyWarning
import warnings
warnings.simplefilter(action="ignore", category=SettingWithCopyWarning)
import plotly
plotly.offline.init_notebook_mode()
def clean_fips(overdose_df):
for i in range(len(overdose_df['County Code'])):
if len(overdose_df['County Code'].iloc[i]) < 5:
overdose_df['County Code'].iloc[i] = '0' + overdose_df['County Code'].iloc[i]
if len(overdose_df['State Code'].iloc[i]) < 2:
overdose_df['State Code'].iloc[i] = '0' + overdose_df['State Code'].iloc[i]
return overdose_df
state_abbrev = {
"Alabama": "AL", "Alaska": "AK", "Arizona": "AZ", "Arkansas": "AR", "California": "CA", "Colorado": "CO",
"Connecticut": "CT", "Delaware": "DE", "Florida": "FL", "Georgia": "GA", "Hawaii": "HI", "Idaho": "ID",
"Illinois": "IL", "Indiana": "IN", "Iowa": "IA", "Kansas": "KS", "Kentucky": "KY", "Louisiana": "LA",
"Maine": "ME", "Maryland": "MD", "Massachusetts": "MA", "Michigan": "MI", "Minnesota": "MN",
"Mississippi": "MS", "Missouri": "MO", "Montana": "MT", "Nebraska": "NE", "Nevada": "NV",
"New Hampshire": "NH", "New Jersey": "NJ", "New Mexico": "NM", "New York": "NY", "North Carolina": "NC",
"North Dakota": "ND", "Ohio": "OH", "Oklahoma": "OK", "Oregon": "OR", "Pennsylvania": "PA",
"Rhode Island": "RI", "South Carolina": "SC", "South Dakota": "SD", "Tennessee": "TN", "Texas": "TX",
"Utah": "UT", "Vermont": "VT", "Virginia": "VA", "Washington": "WA", "West Virginia": "WV",
"Wisconsin": "WI", "Wyoming": "WY", "District of Columbia": "DC", "American Samoa": "AS", "Guam": "GU",
"Northern Mariana Islands": "MP", "Puerto Rico": "PR", "United States Minor Outlying Islands": "UM",
"U.S. Virgin Islands": "VI"}
Import and clean the opioid overdose data from the CDC Wonder MCD mortality dataset using the multiple cause-of-death classification codes:
opioidOverdoses = pd.read_csv("Opioid_Overdoses_03082022.tsv", sep="\t")
opioidOverdoses = opioidOverdoses.drop(['Notes', 'Year Code'], axis=1)
opioidOverdoses = opioidOverdoses[opioidOverdoses['State'].notna()]
opioidOverdoses = opioidOverdoses[opioidOverdoses['Deaths'] != 'Missing']
opioidOverdoses = opioidOverdoses.astype({'County Code': 'int32', 'Year': 'int32', 'Deaths': 'int32', 'Population': 'int32', 'State Code': 'int32'})
opioidOverdoses = opioidOverdoses.astype({'County Code': 'str', 'State Code': 'str'})
opioidOverdoses = opioidOverdoses.sort_values(by=['Year', 'County Code']).reset_index(level=None)
opioidOverdoses = clean_fips(opioidOverdoses)
opioidOverdoses = opioidOverdoses.groupby(['State', 'Year'])[['Deaths', 'Population']].sum().reset_index()
opioidOverdoses['OverdoseMortalityRate'] = ((opioidOverdoses['Deaths']/opioidOverdoses['Population'])*(10**5)).round(decimals=2)
opioidOverdoses['Abbreviations'] = opioidOverdoses['State'].map(state_abbrev)
Import and clean the Johns Hopkins University dataset for COVID-19 total cases and total deaths by state as of 12/31/2020.
Data found here - https://github.com/CSSEGISandData/COVID-19
covid_cases = pd.read_csv("United_States_COVID-19_Cases_and_Deaths_by_State_over_Time.csv")
covid2020 = covid_cases[covid_cases['submission_date'] == '12/31/2020'][['submission_date', 'state', 'tot_cases', 'tot_death']]
covid2020 = covid2020.rename(columns={"state": "Abbreviations", "tot_cases": "Cases", "tot_death": "Deaths"})
covid2020 = pd.merge(covid2020, opioidOverdoses[opioidOverdoses['Year'] == 2020][['Abbreviations', 'Population']], on='Abbreviations', how='left')
covid2020['CovidCaseRate'] = ((covid2020['Cases']/covid2020['Population'])*10**5).round(decimals=2)
covid2020['CovidMortalityRate'] = ((covid2020['Deaths']/covid2020['Population'])*10**5).round(decimals=2)
covid2020['CovidDeathsPerCases'] = ((covid2020['Deaths']/covid2020['Cases'])*10**5).round(decimals=2)
covid2020 = covid2020.dropna()
df = pd.merge(covid2020, opioidOverdoses[opioidOverdoses['Year'] == 2020][['Abbreviations', 'OverdoseMortalityRate']], on='Abbreviations', how='left')
temp_df = pd.DataFrame()
temp_df[['Abbreviations', '2019OverdoseMortalityRate']] = opioidOverdoses[opioidOverdoses['Year'] == 2019][['Abbreviations', 'OverdoseMortalityRate']]
df = pd.merge(df, temp_df[['Abbreviations', '2019OverdoseMortalityRate']], on='Abbreviations', how='left')
df['2020OverdoseChange'] = (100*(df['OverdoseMortalityRate'] - df['2019OverdoseMortalityRate']) / df['2019OverdoseMortalityRate']).round(1)
df = df[['Abbreviations', 'OverdoseMortalityRate', '2020OverdoseChange', 'Population', 'CovidCaseRate', 'CovidMortalityRate', 'CovidDeathsPerCases']]
First we will examine the data and correlation coefficients related to opioid overdose mortality rates, COVID-19 case rates, and COVID-19 mortality rates.
The specific data metrics are:
df.head()
| Abbreviations | OverdoseMortalityRate | 2020OverdoseChange | Population | CovidCaseRate | CovidMortalityRate | CovidDeathsPerCases | |
|---|---|---|---|---|---|---|---|
| 0 | ID | 10.80 | 15.0 | 990491.0 | 14243.14 | 144.98 | 1017.88 |
| 1 | NE | 7.66 | 39.3 | 1083838.0 | 15429.70 | 152.33 | 987.25 |
| 2 | AL | 18.14 | 46.1 | 2646616.0 | 13678.30 | 271.52 | 1985.02 |
| 3 | MT | 10.89 | -7.6 | 284620.0 | 28653.99 | 337.64 | 1178.35 |
| 4 | ME | 32.48 | 22.5 | 1222403.0 | 1979.79 | 28.39 | 1433.83 |
The correlation coefficients between all of the data metrics and the opioid overdose mortality rate and year-over-year change in opioid overdose mortality rates will let us know if there exists any linear or monotonic relation between these metrics.
df.corr(method='pearson')[['OverdoseMortalityRate', '2020OverdoseChange']]
| OverdoseMortalityRate | 2020OverdoseChange | |
|---|---|---|
| OverdoseMortalityRate | 1.000000 | 0.020725 |
| 2020OverdoseChange | 0.020725 | 1.000000 |
| Population | -0.061999 | 0.206315 |
| CovidCaseRate | -0.418324 | -0.055864 |
| CovidMortalityRate | -0.307053 | -0.070132 |
| CovidDeathsPerCases | 0.353524 | -0.226786 |
df.corr(method='spearman')[['OverdoseMortalityRate', '2020OverdoseChange']]
| OverdoseMortalityRate | 2020OverdoseChange | |
|---|---|---|
| OverdoseMortalityRate | 1.000000 | -0.076495 |
| 2020OverdoseChange | -0.076495 | 1.000000 |
| Population | 0.195294 | 0.146507 |
| CovidCaseRate | -0.423801 | 0.056519 |
| CovidMortalityRate | -0.184072 | -0.089268 |
| CovidDeathsPerCases | 0.480633 | -0.160048 |
As we can see, there is no significant correlation between the year-over-year percent change in opioid overdose deaths between 2019 and 2020 and any of the other statewide metrics.
There is a slight to moderate correlation beween the Opioid Overdose Mortality rates in 2020 and the following statewide metrics.
In other words, this suggests that states with higher rates of Opioid Overdose Mortalities had a slightly higher rates of COVID-19 Deaths Per Cases and slightly lower rates for COVID-19 Cases.
First let us visualize and compare opioid overdose mortality rates in 2020 with rates of COVID-19 cases and rates of COVID-19 Deaths Per Cases.
fig = px.choropleth(df, locations='Abbreviations', locationmode="USA-states",
color='CovidCaseRate', range_color=(0, 20000), labels={'CovidCaseRate': 'Case Rate'},
scope="usa",color_continuous_scale='reds', title='COVID-19 Case Rate Per 100K')
fig.show()
Comparing the images above and below we can see that states with particularly high rates of COVID-19 cases in the Northern Great Plains area have some of the lowest rates of opioid overdose deaths. Additionally, the states in Central and Northern Appalachia have some of the highest rates of opioid overdose deaths and moderately low rates of COVID-19 cases.
fig = px.choropleth(df, locations='Abbreviations', locationmode="USA-states",
color='OverdoseMortalityRate', range_color=(0, 40), labels={'OverdoseMortalityRate': 'Mortality Rate'},
scope="usa",color_continuous_scale='purp', title='Opioid Overdose Mortality Rate Per 100K')
fig.show()
fig = px.choropleth(df, locations='Abbreviations', locationmode="USA-states",
color='CovidDeathsPerCases', range_color=(500, 2500), labels={'CovidDeathsPerCases': 'Mortality Rate'},
scope="usa",color_continuous_scale='reds', title='COVID-19 Mortality Rate Per 100K Cases')
fig.show()
It is slightly more difficult to clearly visualize the trends between higher opioid overdose mortality rates and COVID-19 case mortality rates, however the distinction is still clear that the states with relatively higher rates of opioid related deaths also have relatively higher COVID-19 case mortality rates. This correlation is particularly more distinct amongst states in the Southwest (New Mexico and Arizona) and the Northeast (Pennsylvania, New York, and Maryland). The moderately high correlation coefficients further indicate that the states with the highest rate of opioid overdose mortalities are also the states with the highest rates of COVID-19 case mortalities. From this information it would not be unreasonable to conclude that the states with high rates of opioid overdose mortalities and COVID-19 case mortalities simply lacked access to the public healthcare infrastructure necessarry to address either of these public health crises.
However, it is difficult to explain why states with a high rate of opioid related deaths would simultaneously have low COVID-19 case rates per 100K residents and high COVID-19 mortality rates per 100K cases, particularly when there is little correlation between rates of COVID-19 cases and deaths per cases (see below).
df.corr(method='pearson')[['CovidCaseRate', 'CovidDeathsPerCases']].iloc[[3,5]]
| CovidCaseRate | CovidDeathsPerCases | |
|---|---|---|
| CovidCaseRate | 1.00000 | -0.25157 |
| CovidDeathsPerCases | -0.25157 | 1.00000 |
Additional issues with comparing rates of opioid overdoses and COVID-19 deaths include: 1) Different state's have different standards by which they record COVID-19 cases and COVID-19 deaths. 2) The COVID-19 pandemic affected the different regions of the U.S. in waves, so comparing COVID-19 metrics using 12/31/2020 as a temporal deadline is inherently arbitrary.
Therefore, in regards to this analysis it appears unlikely that there is any meaningful correlation between rates of opioid overdose mortalities and rates of COVID-19 cases or COVID-19 deaths per cases or at the very least that there is no conclusive explanation for this correlation from the data available.
**Further work to come regarding the opioid overdose mortality rates, key economic indicators, and the economic impact of COVID-19.